﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using XuanFe.Database.Model;

namespace XuanFe.Database.DAL
{
    public class RoleFunctionDAL
    {
        private const string SQL_SELECT_WS_ROLE_FUNCTION = "select ROF_ROLE_ID,ROF_FUNCTION_ID,ROF_RCRE_STAFF_ID,ROF_RCRE_TIME,ROF_LCHG_STAFF_ID,ROF_LCHG_TIME from WS_ROLE_FUNCTION where 1=1";
        private const string SQL_INSERT_WS_ROLE_FUNCTION = "insert into WS_ROLE_FUNCTION(ROF_ROLE_ID,ROF_FUNCTION_ID,ROF_RCRE_STAFF_ID,ROF_RCRE_TIME) values(@RoleId,@FunctionId,@LastStaffId,@LastTime)";
        private const string SQL_DELETE_WS_ROLE_FUNCTION = "delete from WS_ROLE_FUNCTION where 1=1";

        private const string SQL_WHERE_ROLE_ID = " and ROF_ROLE_ID=@RoleId ";

        private const string PARM_ROLE_ID = "@RoleId";
        private const string PARM_FUNCTION_ID = "@FunctionId";
        private const string PARM_LAST_STAFF_ID = "@LastStaffId";
        private const string PARM_LAST_TIME = "@LastTime";

        Nullable<DateTime> NullDateTime = null;
        private static RoleFunctionDAL instance = new RoleFunctionDAL();
        /// <summary>
        /// 初始化实例
        /// </summary>
        /// <returns></returns>
        public static RoleFunctionDAL Instance
        {
            get
            {
                return instance;
            }
        }
        public List<RoleFunctionModel> Get(string roleId)
        {
            StringBuilder sbSQL = new StringBuilder(SQL_SELECT_WS_ROLE_FUNCTION);
            sbSQL.Append(SQL_WHERE_ROLE_ID);

            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_ROLE_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = roleId });

            using (DataSet ds = MySQLHelper.Instance.ExecuteDataSet(MySQLHelper.Instance.ConnectionString, CommandType.Text, sbSQL.ToString(), parms.ToArray()))
            {
                DataTable dt = ds.Tables[0];
                List<RoleFunctionModel> list = dt.AsEnumerable().Select(t => new RoleFunctionModel
                {
                    RoleId = Convert.ToString(t.ItemArray[0]),
                    FunctionId = Convert.ToString(t.ItemArray[1]),
                    CreatedStaffId = Convert.ToString(t.ItemArray[2]),
                    CreatedDate = Convert.ToDateTime(t.ItemArray[3]),
                    LastModifiedStaffId = t.IsNull(4) ? string.Empty : Convert.ToString(t.ItemArray[4]),
                    LastModifiedDate = t.IsNull(5) ? NullDateTime : Convert.ToDateTime(t.ItemArray[5])
                }).ToList<RoleFunctionModel>();

                return list;
            }
        }
    
        public int Insert(DbTransaction trans, RoleFunctionModel model)
        {
            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_ROLE_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.RoleId });
            parms.Add(new MySqlParameter(PARM_FUNCTION_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.FunctionId });
            parms.Add(new MySqlParameter(PARM_LAST_STAFF_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.LastModifiedStaffId });
            parms.Add(new MySqlParameter(PARM_LAST_TIME, MySqlDbType.DateTime) { Direction = ParameterDirection.Input, Value = model.LastModifiedDate });

            return MySQLHelper.Instance.ExecuteNonQuery(trans, CommandType.Text, SQL_INSERT_WS_ROLE_FUNCTION, parms.ToArray());
        }
        public int Delete(DbTransaction trans, string roleId)
        {
            StringBuilder sbSQL = new StringBuilder(SQL_DELETE_WS_ROLE_FUNCTION);
            sbSQL.Append(SQL_WHERE_ROLE_ID);

            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_ROLE_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = roleId });

            return MySQLHelper.Instance.ExecuteNonQuery(trans, CommandType.Text, sbSQL.ToString(), parms.ToArray());
        }
    }
}
